Process File From Bank (Update Status of Checks) - CKR 2

Processing a File from a Bank

CSV File Format Bank Code for Check Processing

Running the Process File From Bank Application (CKR 2)

Troubleshooting

Associated Files

This option allows you to import a check reconciliation file from your financial institution and automatically mark checks as cleared.

Customers can choose between the default bank format and three other formats (Bank of America, or a 12 digit paid amount with either a 10 digit or 13 digit account number).

  • Customers using the default bank format should place the file received from their financial institution into the following folder on the IFS: home/Dancik/bankrec/DFT.
  • Otherwise, the file needs to be placed in the one of the following IFS folders
    • For Bank of America: home/Dancik/bankrec/BOA/Bankrec.dta. Each division or branch Bank of America may have a different format. The format from Bank of America must be in this order. If it is not, contact KerridgeNC to add additional formats.
    • For Wells Fargo (bank code WF): home/Dancik/bankrec/WF/Bankrec.dta
    • For 10 digit formats: home/Dancik/bankrec/C10/Bankrec.dta
    • For 13 digit formats: home/Dancik/bankrec/C13/Bankrec.dta

Each file must be named Bankrec.dta.

The layouts for the five bank reconciliation options are shown below.

Default

(Characters)

C10 (10 digit acct#)

(Characters)

C13 (13 digit acct#

(Characters)

Bank of America

(Characters)

Wells Fargo

(Characters)
(1-10) Account # (1-10) Account # (1-12) Account # (1-10) Check#

Account#

Comes from the Header (H1) record. Characters 4-13.
(18-23) Check # (18-23) Check # (16-25) Check # (11-21) Amount

From the Detail format (R12 - the first 3 characters)

Check amount (4-13)

(24-33)

Check Amount

(24-35)

Check Amount

(26-37)

Check Amount

(22-27)

Issue Date

From the Detail format (R12)

Transaction Type Code(14-16)

(Currently not used)

(34-39)

Date Paid

(36-41)

Date Paid

(38-43)

Date Paid

(28-33)

Date Paid

From the Detail format (R12)

Issue Date(17-22)

(Currently not used)
     

(34-45)

Account #

From the Detail format (R12)

Check Amount (23 -32)
       

From the Detail format (R12)

Paid Date (33-38)

Processing a File from a Bank

  1. On the Check Reconciliation menu, select option 2 - Process File From Bank (update status of checks).
  2. A listing of the bank reconciliation formats that are supported appears.

  3. Select a bank code to continue using the Bank Reconciliation application. Once set, this does not have to be selected again for subsequent check reconciliation runs.
  4. The Co# and Acct# fields have been added to further define the default bank for check reconciliation down to the account level. The Co# and Acct# fields also allow you to use the same check number across multiple accounts.
  5. View the edit report, located under the user's spooled files, for any exception errors.

CSV File Format Bank Code for Check Processing

The CSV bank code option allows you to import a CSV file from any bank.

To use the CSV bank code option, the information from the financial institution has to be in the following 5 column format.

Notes about how the data should be formatted

  • Since this is a CSV formatted spreadsheet commas cannot be used in any of the columns such as in the Amount. If a comma is used, another column will be added to the spreadsheet. For example, if an amount comes in from the bank with a comma such as 5,228.99 two columns will be created. One with 5 and the second with 228.99.
  • The CSV file must contain a header record and the header entries must be in the same order as shown above.
  • The Date format is MM/DD/YYYY and there must be a 0 in the months and days that are single digits from 1 - 9. Example 01/08/2016.
  • The Amount column can contain two decimal places. If there is not a decimal in the amount the number is treated as a whole number. For example, in the example above, if the decimal would have been left out of the amount of 5228.99 the amount would show up as 522988.
  • The check number must be no longer than 6 digits.
  • If a row has an invalid date, no amount, no check number or the check number is longer than 6 digits that row is skipped.

Saving the CSV file

After the CSV file is properly formatted, save it to the following IFS directory:

  • \home\DancikBankrec\CSV\Bankrec.csv

After it is processed via the Process File From Bank application (CKR 2) it is renamed to:

  • \home\Dancik\Bankrec\CSV\Bankrec.OLD

The CSV file must be located in the correct IFS directory and be named Bankrec.csv. This is the directory and name the system uses to locate the CSV file.

Running the Process File From Bank Application (CKR 2)

  1. Access the Check Reconciliation menu and select option 2 - Process File From Bank (update status of checks).
  2. Enter a company and GL cash account number used to clear checks for the CSV.

    3. If you have more than one cash account, you will need to create a different CSV file.

  3. Enter an X in the Default Bank Code field.

  4. Press Enter and then F7. The system looks in the IFS directory \home\Dancik\Bankrec\CSV\ for a file entitled Bankrec.csv.
  5. If the CSV file is found, the checks in the file are updated to a status of CA and the date cashed is noted.

    6. A spool file report is also generated that shows the following about the reconciled checks:

    • Check Number
    • Check Amount
    • Date Paid
    • GL Account
    • Amount Paid
    • Amount Paid difference

Troubleshooting

  • Error Message: There are no resources to process. Open bank file for data
    • Solution: it means there is not a file available for processing. Check the following:
  • File name is not labeled as Bankrec.dta
    • File is not loaded onto the IFS drive.

Associated Files